package javaseof10day.day9.am.demo4;

//对于表进行数据操作的类
import java.sql.*;
import java.util.*;

public class ActionDAO {
	private Connection con;

	public ActionDAO() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// 加载驱动

			String conStr = "jdbc:mysql://localhost:3306/it123blog?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false";

			// 配置连接字符串
			String user = "root";
			// 配置用户名
			String password = "root";
			// 配置用户的访问密码
			con = DriverManager.getConnection(conStr, user, password);
			// 创建数据库连接对象
		} catch (ClassNotFoundException e) {// 捕捉处理驱动类未找到异常
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {// 捕捉处理数据连接或者操作异常
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	// 精确查找
	public ArrayList<Books> findStuBySno(String sno) {

		ArrayList<Books> list = new ArrayList<Books>();
		try {
			Statement st = con.createStatement();
			String strSQL = "select * from books where name like '%" + sno + "%';";
			ResultSet rs = st.executeQuery(strSQL);
			while (rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				int price = rs.getInt(3);
				int num = rs.getInt(4);
				String dates = rs.getString(5);
				String style = rs.getString(6);
				Books stu = new Books(id, name, price, num, dates, style);
				list.add(stu);
			}
			st.close();
			// 关闭SQL语句执行对象
			// con.close();
			// 关闭数据库连接对象
		} catch (SQLException e) {// 捕捉处理数据连接或者操作异常
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;

	}

	// 模糊查找
	public ArrayList<Books> findStuBySname(String sstyle) {
		ArrayList<Books> result = new ArrayList<Books>();
		try {
			Statement st = con.createStatement();
			String strSQL = "select * from books where style like '%" + sstyle + "%';";
			ResultSet rs = st.executeQuery(strSQL);
			while (rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				int price = rs.getInt(3);
				int num = rs.getInt(4);
				String dates = rs.getString(5);
				String style = rs.getString(6);
				Books stu = new Books(id, name, price, num, dates, style);
				result.add(stu);
			}
			st.close();
			// 关闭SQL语句执行对象
			// con.close();
			// 关闭数据库连接对象
		} catch (SQLException e) {// 捕捉处理数据连接或者操作异常
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return result;
	}

	public void addStudent(Books stu) {
		try {
			PreparedStatement ps = con
					.prepareStatement("insert into books( name, price, num,dates, style) values (?,?,?,?,?);");
			// 创建SQL语句执行对象
			ps.setString(1, stu.getName());
			ps.setInt(2, (stu.getPrice()));
			ps.setInt(3, (stu.getNum()));
			ps.setString(4, (stu.getDates()));
			ps.setString(5, stu.getStyle());

			ps.execute();
			ps.close();
			// 关闭SQL语句执行对象
			// con.close();
			// 关闭数据库连接对象
		} catch (SQLException e) {// 捕捉处理数据连接或者操作异常
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void delStudent(String sname) {
		try {
			PreparedStatement ps = con.prepareStatement("delete from books where name=?");
			// 创建SQL语句执行对象
			ps.setString(1, sname);
			ps.execute();
			ps.close();

			// 关闭SQL语句执行对象
			// con.close();
			// 关闭数据库连接对象
		} catch (SQLException e) {// 捕捉处理数据连接或者操作异常
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void updateStudent(Books stu) {

		try {
			PreparedStatement ps = con.prepareStatement("update books set price = ?,num =?, style =? where name = ?");
			// 创建SQL语句执行对象
			ps.setInt(1, stu.getPrice());
			ps.setInt(2, stu.getNum());
			ps.setString(3, stu.getStyle());
			ps.setString(4, stu.getName());
			ps.execute();
			ps.close();
			// 关闭SQL语句执行对象
			// con.close();
			// 关闭数据库连接对象
		} catch (SQLException e) {// 捕捉处理数据连接或者操作异常
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}
